I'm currently working on a project of building a data mart using SSIS 2008 r2 from ERP database.
These datamarts will have to be used in every database that has the same structure (same tables/fields).
The problem is that the source table name changes based on the database connected to. But the table structure (metadata) does not change between those environments.
For example,
Database 1 item table name is company_name1$item
Database 2 item table name is company_name2$item
I'm looking for a solution that makes the datamart can be used for both databases.
I did some research; I found that my solution can be like explained in the picture
As you in the diagram picture, there are steps to follow to build the datamart:
To build a datamart, you will to extract the informations and process
them through ETL to have the desired datamart. This procedure needs
to be applied to all databases
This procedure can be done by a table in the erp database called
object This table has all kind of informations about the database
tables but the most useful fields are table id,table name and company
name
Now, I want to use this table id, Name Company to select the proper table without having to selected data based on table name but there no linking between object table and other tables, so you kind of need to create a some kind of code or something to be able to create the link.
In a nut shell, when I want to use package for a databse , I have insert is the company name and the package will be run to fill my datamart.
As some suggested, I should use a sql procedure of this type
USE YourDataBaseName;
CREATE PROCEDURE GetMyData
@aCompany NVARCHAR(50) = NULL,
@tbl_ID INT = 0
AS
SET NOCOUNT ON;
DECLARE @tableName NVARCHAR(50)
DECLARE @aQuery NVARCHAR(4000)
SELECT @tableName = [Name]
FROM [object]
WHERE [Company Name] = @aCompany AND [ID] = @tbl_ID
SET @aQuery = 'SELECT * FROM ' + @tableName
EXEC(@aQuery)
GO
Other one which was also interesting, said that SSIS allows for the use of a variable name in your source and destinations. I just simply need to provide the mechanism for making that value change. Perhaps that's a foreach enumerator containing the data flow or a parameter set at run-time.
Both suggested solution are great but still not clear on how to do that , I hope now that I have explained my question, I would get a more specific answer